Imports System.IO
Imports System.Data.SqlClient
Imports System.Configuration

Class SqlServerTableProcessor
    Inherits TableProcessor

    Dim cnx As SqlConnection
    Dim cmd As SqlCommand

    Public Overrides Sub Initialize()
        cnx = New SqlConnection(ConfigurationManager.ConnectionStrings("SQLEXPRESS").ConnectionString)
        cmd = New SqlCommand()
        cmd.Connection = cnx
        cnx.Open()
    End Sub

    Public Overrides Sub Process(ByVal xmlOrigen As FileInfo, ByVal ti As TableInfo)
        Console.WriteLine("Escribiendo tabla " & ti.Name)

        Dim tw As TextWriter


        tw = New StringWriter()
        ti.WriteDrop(tw)
        cmd.CommandText = tw.ToString
        Try
            cmd.ExecuteNonQuery()
        Catch ex As SqlException
            If Not ex.Message.StartsWith("Cannot drop the table") Then
                Throw
            End If
        End Try
        tw.Close()

        tw = New StringWriter()
        ti.WriteCreate(tw)
        cmd.CommandText = tw.ToString
        cmd.ExecuteNonQuery()
        tw.Close()

        Dim max As Integer = 0
        Dim l = ti.GetInserts
        For Each ins In l
            max = Math.Max(max, ins.Sql.Length)
            cmd.CommandText = ins.Sql
            Try
                cmd.ExecuteNonQuery()
            Catch ex As SqlException
                Trace.WriteLine(ex.Message)
                Trace.WriteLine(cmd.CommandText)
            End Try
        Next
    End Sub

    Public Overrides Sub Finish()
        cnx.Close()
    End Sub

End Class
